﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using S.Common.Enums.Initial;
using S.Common.helper;
using S.Initial.Domain;
using S.Initial.Infrastructure.Dto;
using S.Initial.Infrastructure.Interface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
using static S.Initial.Infrastructure.Impl.BreedingMothersRepository;

namespace S.Initial.Infrastructure.Impl
{
    /// <summary>
    /// 种母的仓储层接口
    /// </summary>
    public class BreedingMothersRepository : IBreedingMothersRepository
    {
        private readonly IConfiguration configuration;
        private readonly MyDbcontext db;

        public BreedingMothersRepository(IConfiguration configuration, MyDbcontext db)
        {
            this.configuration = configuration;
            this.db = db;
        }
        public IDbConnection Connection
        {
            get
            {
                return new SqlConnection(configuration.GetValue<string>("ConnectionString"));
            }
        }

        #region 期初

        #region 批量添加
        /// <summary>
        /// 批量添加
        /// </summary>
        /// <param name="Breedingmothers"></param>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> AddAllBreedingmothers(List<BreedMaleAndFemaleStart> Breedingmothers)
        {
            using (IDbConnection dbConnection = Connection)
            {
                try
                {
                    dbConnection.Open();
                    var sql = "INSERT INTO [dbo].[BreedMaleAndFemaleStart]([Type],[MarkId],[EarTag],[BuildingName],[ColumnName],[ReplantingDate],[Weight],[Remark],[DocumentNumber],[SerialNumber],[OpeningDate],[CreateBy],[CreateDate],[LogicalDeletion],[Stage])VALUES";
                    //定义一个字符串类型的values
                    StringBuilder values = new StringBuilder();
                    foreach (var record in Breedingmothers)
                    {
                        int Stages = 0;
                        if (!string.IsNullOrEmpty(record.Stage.ToString()))
                        {
                            Stages = (int)record.Stage;
                        }
                        // 构建每个记录的值部分
                        string value = $@"('{Convert.ToInt32(record.Type)}','{record.MarkId}','{record.EarTag}','{record.BuildingName}','{record.ColumnName}','{record.ReplantingDate.ToString("yyyy-MM-dd HH:mm:ss")}','{record.Weight}','{record.Remark}','{record.DocumentNumber}','{record.SerialNumber}','{record.OpeningDate.ToString("yyyy-MM-dd HH:mm:ss")}','{record.CreateBy}','{record.CreateDate.ToString("yyyy-MM-dd HH:mm:ss")}','{record.LogicalDeletion}','{Stages}')";
                        //每循环一次都要把value的值添加到values里面
                        values.Append(value);
                        values.Append(",");
                    }
                    // 为了将集合中的中括号去掉
                    string valuesString = string.Join(",", values);
                    // 拼接sql语句
                    sql += valuesString.ToString().TrimEnd(',');
                    //返回受影响的行数
                    return dbConnection.Execute(sql);
                }
                catch (Exception)
                {
                    throw;
                }
                finally { dbConnection.Close(); }

            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="Id"></param>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> DeleteBreedingmothers(int Id)
        {
            using (IDbConnection dbConnection = Connection)
            {
                try
                {
                    dbConnection.Open();
                    var sql = "DELETE FROM [dbo].[BreedMaleAndFemaleStart] WHERE StartId = @id";
                    return dbConnection.Execute(sql, new { @id = Id });
                }
                catch (Exception)
                {
                    throw;
                }
                finally { dbConnection.Close(); }
            }
        }

        #endregion

        #region 查询显示+分页

        /// <summary>
        /// 查询显示+分页
        /// </summary>
        /// <param name="Type"></param>
        /// <param name="EarTag"></param>
        /// <param name="KaiDate"></param>
        /// <param name="JieDate"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public async Task<(IEnumerable<BreedMaleAndFemaleStart> items, int totalCount, int pageCount)> GetAllBreedingmothersBy(int Type, string EarTag, string? KaiDate, string? JieDate, int page, int size)
        {
            using (IDbConnection dbConnection = Connection)
            {
                try
                {
                    dbConnection.Open();

                    // 获取满足条件的总记录数
                    var countSql = "SELECT COUNT(*) FROM [BreedMaleAndFemaleStart] where 1=1 ";
                    if (Type != 0)
                    {
                        countSql += " and Type = @Type";
                    }
                    if (!string.IsNullOrEmpty(EarTag))
                    {
                        countSql += " AND EarTag LIKE @EarTag";
                    }
                    if (!string.IsNullOrEmpty(KaiDate) && !string.IsNullOrEmpty(JieDate))
                    {
                        countSql += " AND ReplantingDate >= @KaiDate AND ReplantingDate <= @JieDate";
                    }
                    var totalCount = await dbConnection.ExecuteScalarAsync<int>(countSql, new { @Type = Type, @EarTag = EarTag, @KaiDate = KaiDate, @JieDate = JieDate });

                    // 计算总页数
                    var pageCount = (int)Math.Ceiling(totalCount * 1.0 / size);

                    // 构建分页查询的SQL语句
                    var offset = (page - 1) * size;
                    var fetchSql = "SELECT [StartId],[Type],[MarkId],[EarTag],[BuildingName],[ColumnName],[ReplantingDate],[Weight],[Remark],[DocumentNumber],[SerialNumber],[OpeningDate],[CreateBy],[CreateDate],[UpdateBy],[UpdateDate],[AuditBy],[AuditDate],[LogicalDeletion],[Stage] FROM [BreedMaleAndFemaleStart] WHERE 1=1 ";
                    if (Type != 0)
                    {
                        fetchSql += " and Type = @Type";
                    }
                    if (!string.IsNullOrEmpty(EarTag))
                    {
                        fetchSql += " AND EarTag LIKE @EarTag";
                    }
                    if (!string.IsNullOrEmpty(KaiDate) && !string.IsNullOrEmpty(JieDate))
                    {
                        fetchSql += " AND ReplantingDate >= @KaiDate and ReplantingDate <= @JieDate";
                    }
                    fetchSql += " ORDER BY [StartId] DESC";
                    fetchSql += " OFFSET @Offset ROWS FETC" +
                        "H NEXT @Size ROWS ONLY";
                    var sql = $"{fetchSql}";
                    // 执行分页查询
                    var items = await dbConnection.QueryAsync<BreedMaleAndFemaleStart>(sql, new { @Type = Type, @EarTag = EarTag, @KaiDate = KaiDate, @JieDate = JieDate, @Offset = offset, @Size = size });

                    return (items, totalCount, pageCount);
                }
                catch (Exception)
                {
                    throw;
                }
                finally { dbConnection.Close(); }
            }
        }

        #endregion

        #region 根据id查询

        /// <summary>
        /// 根据id查询
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<BreedMaleAndFemaleStart> GetBreedingmothersByIdAsync(int Id)
        {
            var FindId = db.BreedMaleAndFemaleStart.Find(Id);
            return FindId;
        }
        #endregion

        #region 修改

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="Breedingmothers"></param>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> UpdateBreedingmothers(BreedMaleAndFemaleStart Breedingmothers)
        {
            try
            {
                Breedingmothers.UpdateBy = "蔡志坤";
                Breedingmothers.UpdateDate = DateTime.Now;
                var reqult = db.BreedMaleAndFemaleStart.Update(Breedingmothers);
                return db.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        #endregion

        #region 批量删除

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="startIds"></param>
        /// <returns></returns>
        public async Task<int> DeleteAllBreedingmothers(IEnumerable<string> startIds)
        {
            using (IDbConnection dbConnection = Connection)
            {
                try
                {
                    dbConnection.Open();
                    var sql = "DELETE FROM [BreedMaleAndFemaleStart] WHERE [EarTag] IN @StartIds";
                    return await dbConnection.ExecuteAsync(sql, new { StartIds = startIds });
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    dbConnection.Close();
                }
            }
        }

        #endregion

        #endregion

        #region 审核

        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Task<int> AddauditBy(BreedMaleAndFemaleStart breedMaleAndFemaleStart)
        {
            breedMaleAndFemaleStart.AuditBy = "蔡志坤";
            breedMaleAndFemaleStart.AuditDate = DateTime.Now;
            db.BreedMaleAndFemaleStart.Update(breedMaleAndFemaleStart);
            return db.SaveChangesAsync();
        }

        #endregion

        #region 存栏统计
        /// <summary>
        /// 存栏统计
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<IEnumerable<InventoryStatisticsDto>> ShowInventoryStatistics(string? Generic,int Variety,DateTime? KDate, DateTime? JDate,string? Cha,string? ChaZhi, int pageNumber, int pageSize)
        {
            using (IDbConnection dbConnection = Connection)
            {
                try
                {
                    dbConnection.Open();
                    var data = $"SELECT {Generic} aaa," +
                        $"COUNT(sr.ShiftToBuild) RuRoundup, " +
                        $"COUNT(dt.TransferDate) RuReplanting," +
                        $"COUNT(lrm.BirthDate) Ruborn," +
                        $"COUNT(sr.TurnOutBuild) ChuRoundup, " +
                        $"COUNT(dm.DeathType) Chudeath," +
                        $"COUNT(em.DepartureState) Chueliminate," +
                        $"COUNT(ss.SaleDate) Rureturn," +
                        $"COUNT(pf.DateOfPurchase) Ruprocurement," +
                        $"COUNT(c.BuildingName) Beginning ," +
                        $"COUNT(CASE WHEN sf.PresenceState = 3 THEN 1 ELSE NULL END) AS chusale " +
                        $"FROM " +
                        $"SheepFileModel sf LEFT JOIN " +
                        $"MaternalMating mm ON sf.EarTag = mm.EarTag  LEFT JOIN " +
                        $"BreedMaleAndFemaleStart bms ON bms.EarTag = sf.EarTag LEFT JOIN " +
                        $"MaternalWeaning mw ON sf.EarTag = mw.EarTag LEFT JOIN " +
                        $"LambAblactation la ON sf.EarTag = la.EarTag LEFT JOIN " +
                        $"BodyMeasureModel bm ON sf.EarTag = bm.EarTag LEFT JOIN " +
                        $"AppearanceModel am ON sf.EarTag = am.EarTag LEFT JOIN " +
                        $"DeathModel dm ON sf.EarTag = dm.EarTag LEFT JOIN " +
                        $"SheepRescission sr ON sf.EarTag = sr.EarTag LEFT JOIN " +
                        $"BreedingHistoryModel bh ON sf.EarTag = bh.EarTag LEFT JOIN " +
                        $"PregnancytestModel pt ON sf.EarTag = pt.EarTag LEFT JOIN " +  
                        $"ParturitionModel p ON sf.EarTag = p.EarTag LEFT JOIN " +
                        $"SheepSaleModels ss ON sf.EarTag = ss.EarTag LEFT JOIN " +
                        $"EliminationModel em ON sf.EarTag = em.EarTag LEFT JOIN " +
                        $"DescendantTransfer dt ON sf.EarTag = dt.EarTag LEFT JOIN " +
                        $"MarkModel er ON sf.EarTag = er.EarTag LEFT JOIN " +
                        $"LambRegistrationModel lrm on lrm.EarTag = sf.EarTag LEFT JOIN " +
                        $"EarTagOrMarkModel eom ON er.MarkId = eom.MarkId LEFT JOIN " +
                        $"PurchaseFormModels pf on sf.EarTag = pf.BatchEarHorns LEFT JOIN " +
                        $"EarTagModel etm ON eom.EarTagId = etm.EarTagId LEFT JOIN " +
                        $"SheepRescission b ON sf.EarTag = b.EarTag LEFT JOIN " +
                        $"ColumnManagementModel c ON b.ShiftToBuildingManagementId = c.BuildingManagementId LEFT JOIN " +
                        $"FieldModel d ON b.ShiftToColumnManagementId = d.ColumnManagementId  LEFT JOIN " +
                        $"ProductionGrading pr ON sf.EarTag = pr.RankEarTag  WHERE 1=1 ";
                    if (Variety != 0)
                    {
                        data += $" AND sf.Variety = {Variety} ";
                    }

                    if (KDate.HasValue)
                    {
                        data += $" AND sf.AdmissionDate >= '{KDate?.ToString("yyyy-MM-dd HH:mm:ss")} ' ";
                    }

                    if (JDate.HasValue)
                    {
                        data += $" AND sf.AdmissionDate <= '{JDate?.ToString("yyyy-MM-dd HH:mm:ss")} ' ";
                    }

                    if (!string.IsNullOrEmpty(Cha)&&!string.IsNullOrEmpty(ChaZhi))
                    {
                        data += $" AND {Cha} = '{ChaZhi}' ";
                    }

                    data += $" Group by {Generic} ";

                    data += $" ORDER BY RuRoundup OFFSET {pageNumber} ROWS FETCH NEXT {pageSize} ROWS ONLY";

                    return await dbConnection.QueryAsync<InventoryStatisticsDto>(data);

                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    dbConnection.Close();
                }
            }
        }
        #endregion

        #region 查询条件值
        /// <summary>
        /// 存栏统计
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<IEnumerable<InventoryStatisticsDto>> ShowChaZhiStatistics(string? Generic)
        {
            using (IDbConnection dbConnection = Connection)
            {
                try
                {
                    dbConnection.Open();
                    var data = $"SELECT {Generic} aaa From " +
                        $"SheepFileModel sf LEFT JOIN " +
                        $"MaternalMating mm ON sf.EarTag = mm.EarTag  LEFT JOIN " +
                        $"BreedMaleAndFemaleStart bms ON bms.EarTag = sf.EarTag LEFT JOIN " +
                        $"MaternalWeaning mw ON sf.EarTag = mw.EarTag LEFT JOIN " +
                        $"LambAblactation la ON sf.EarTag = la.EarTag LEFT JOIN " +
                        $"BodyMeasureModel bm ON sf.EarTag = bm.EarTag LEFT JOIN " +
                        $"AppearanceModel am ON sf.EarTag = am.EarTag LEFT JOIN " +
                        $"DeathModel dm ON sf.EarTag = dm.EarTag LEFT JOIN " +
                        $"SheepRescission sr ON sf.EarTag = sr.EarTag LEFT JOIN " +
                        $"BreedingHistoryModel bh ON sf.EarTag = bh.EarTag LEFT JOIN " +
                        $"PregnancytestModel pt ON sf.EarTag = pt.EarTag LEFT JOIN " +
                        $"ParturitionModel p ON sf.EarTag = p.EarTag LEFT JOIN " +
                        $"SheepSaleModels ss ON sf.EarTag = ss.EarTag LEFT JOIN " +
                        $"EliminationModel em ON sf.EarTag = em.EarTag LEFT JOIN " +
                        $"DescendantTransfer dt ON sf.EarTag = dt.EarTag LEFT JOIN " +
                        $"MarkModel er ON sf.EarTag = er.EarTag LEFT JOIN " +
                        $"LambRegistrationModel lrm on lrm.EarTag = sf.EarTag LEFT JOIN " +
                        $"EarTagOrMarkModel eom ON er.MarkId = eom.MarkId LEFT JOIN " +
                        $"PurchaseFormModels pf on sf.EarTag = pf.BatchEarHorns LEFT JOIN " +
                        $"EarTagModel etm ON eom.EarTagId = etm.EarTagId LEFT JOIN " +
                        $"SheepRescission b ON sf.EarTag = b.EarTag LEFT JOIN " +
                        $"ColumnManagementModel c ON b.ShiftToBuildingManagementId = c.BuildingManagementId LEFT JOIN " +
                        $"FieldModel d ON b.ShiftToColumnManagementId = d.ColumnManagementId  LEFT JOIN " +
                        $"ProductionGrading pr ON sf.EarTag = pr.RankEarTag   Group by {Generic} ";

                    return await dbConnection.QueryAsync<InventoryStatisticsDto>(data);

                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    dbConnection.Close();
                }
            }
        }
        #endregion
    }
}
